/**
 * 
 */
package com.desksoft.ecommerce.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.desksoft.ecommerce.vo.KeyAndValue;
import com.desksoft.utility.common.Common;
import com.desksoft.utility.date.DateUtil;
import com.desksoft.utility.string.StringUtil;

/***************************************
 * 版权所有：杭州迪科软件 保留所有权利
 * 创建日期: 
 * 创建作者：yejw
 * 文件名称：
 * 版本：
 * 功能：
 * 最后修改时间：
 * 修改记录：
 *****************************************/
public class ExcelUtil {
	
	/**
	 * 导出Excel
	 * usage: 
	 * 
		LinkedHashMap<String,String> column = new LinkedHashMap<String,String>();
		column.put("name", "姓名");
		column.put("age", "年龄");
		
		Map<String, Object> map = null;
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
				
		for(Map<String, Object> resultMap : serviceResult.getResult().getRecordsList()){
			map = new HashMap<String, Object>();
			//姓名
			map.put("name", resultMap.get("name"));
			//年龄
			map.put("age", resultMap.get("age"));
			
			list.add(map);
		}
		
		ExcelUtil.exportExcel(this.getResponse(), this.getRequest(), "员工.xls", list, column);
		
	 * 
	 * @param response		HttpServletResponse
	 * @param reqeust		HttpServletRequest
	 * @param exportName	导出名字
	 * @param list			数据源
	 * @param column		绑定规则<列实体名字,Excel标题列名字>
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static void exportExcel(HttpServletResponse response, HttpServletRequest reqeust, 
				String exportName, List<?> list, LinkedHashMap<String,?> column) {
		// 取得输出流
		OutputStream os=null;
		try {
			os = response.getOutputStream(); 
			response.reset();
			response.setContentType("application/octet-stream");
			//设置文件编码
			String fileName = StringUtil.encodeDownLoadName(reqeust, exportName);
			response.addHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");

			//工作区  
			//------XSSFWorkbook wb = new XSSFWorkbook();
			HSSFWorkbook wb = new HSSFWorkbook();
			//创建第一个sheet  
			HSSFSheet sheet = wb.createSheet(exportName);
			int row = 0,cell=0;
			HSSFRow xssRow; 
			//生成第一行  
			xssRow = sheet.createRow(row);
			//标题样式 
			CellStyle headStyle = getCellStyle(wb,HSSFColor.GREY_25_PERCENT.index);
			//内容样式 
			CellStyle bodyStyle = getCellStyle(wb,(short)-1);
			
			for(Iterator<String> iter = column.keySet().iterator();iter.hasNext();){
				String name = iter.next();
				Object o = column.get(name);
				String value;
				int width;
				if(o instanceof String){
					value = o.toString();
					width = (short)((30 * 5) / ((double) 1 / 20));
				}
				else{
					value =((KeyAndValue<String, Integer>)o).getKey();
					width = ((KeyAndValue<String, Integer>)o).getValue();
				}
				HSSFCell excelCell = xssRow.createCell(cell);
				excelCell.setCellStyle(headStyle);
				excelCell.setCellValue(value);
				sheet.setColumnWidth(cell, width);
				cell++;
			}
			row++;
			for(Object entity : list){
				xssRow = sheet.createRow(row);
				Map<String,Object> map;
				if(!(entity instanceof Map)){
					map = Common.entityToMap(entity);
				}
				else{
					map = (HashMap<String,Object>)entity;
				}
				cell=0;
				for(Iterator<String> columnIter = column.keySet().iterator();columnIter.hasNext();){
					String name = columnIter.next();
					String result =null;
					Object obj = map.get(name);
					//给这一行的第一列赋值  
					HSSFCell  excelCell  = xssRow.createCell(cell);
					if(obj ==null){
						result ="";
					}
					else if(obj instanceof Date){
						SimpleDateFormat f = new SimpleDateFormat(DateUtil.yyyy_MM_dd_HH_mm_ss);
						result = f.format(obj);
					}
					else{
						result=obj.toString();
					}
					excelCell.setCellType(Cell.CELL_TYPE_STRING);
					excelCell.setCellValue(result);
					excelCell.setCellStyle(bodyStyle);
					cell++;
				}
				row++;
			}
			
			wb.write(os);
			os.flush(); 
		    os.close();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		finally{
			
		}
	}

	/**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     * 
     * @param file 				读取数据的源Excel
     * @param ignoreRows 		读取数据忽略的行数，比如行头不需要读入 忽略的行数为1
     * @return 					读出的Excel中数据的内容
     * @throws FileNotFoundException
     * @throws IOException
     */
	public static String[][] getExcelData(File file , int ignoreRows){
		try {
			return getExcelData(new FileInputStream(file), ignoreRows, 18);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     * 
     * @param inputStream 		读取数据的源Excel
     * @param ignoreRows 		读取数据忽略的行数，比如行头不需要读入 忽略的行数为1
     * @return 					读出的Excel中数据的内容
     * @throws FileNotFoundException
     * @throws IOException
     */
	public static String[][] getExcelData(InputStream in, int ignoreRows) {
		return getExcelData(in, ignoreRows, 18);
	}
	
	/**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     * 
     * @param inputStream 		读取数据的源Excel
     * @param ignoreRows 		读取数据忽略的行数，比如行头不需要读入 忽略的行数为1
     * @return 					读出的Excel中数据的内容
     * @throws FileNotFoundException
     * @throws IOException
     */
	public static String[][] getExcelData(InputStream in, int ignoreRows, int colLength){
    	final int COL_LENGTH = colLength;
    	
        int rowSize = 0;
        Workbook wb = null;
        List<String[]> result = new ArrayList<String[]>();
        
        try {
            try {
                wb = new HSSFWorkbook(in);
            } catch (Exception e) {
                IOUtils.closeQuietly(in);
                wb = new XSSFWorkbook(in);
            }
            Cell cell = null;
            Sheet st = wb.getSheetAt(0);// 默认的是第一个sheet
            // 第一行为标题，不取
            for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
                Row row = st.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                String[] values = new String[COL_LENGTH];
                Arrays.fill(values, "");
                boolean hasValue = false;
                int end = Math.min(COL_LENGTH, row.getLastCellNum());
                for (short columnIndex = 0; columnIndex < end; columnIndex++) {
                    String value = "";
                    cell = row.getCell(columnIndex);
                    if (cell != null) {
                        // 注意：一定要设成这个，否则可能会出现乱码
                        // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                if (date != null) {
                                    value = new SimpleDateFormat("yyyy-MM-dd")
                                            .format(date);
                                }
                            } else {
                                value = new DecimalFormat("0").format(cell
                                        .getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            // 导入时如果为公式生成的数据则无值
                            if (!cell.getStringCellValue().equals("")) {
                                value = cell.getStringCellValue();
                            } else {
                                value = cell.getNumericCellValue() + "";
                            }
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                            break;
                        default:
                        }
                    } else {
                        value = "";
                    }
                    if (columnIndex == 0 && value.trim().equals("")) {
                        break;
                    }
                    values[columnIndex] = value.trim();
                    hasValue = true;
                }
                if (hasValue) {
                    result.add(values);
                }
            }
        }catch(FileNotFoundException e){
        	e.printStackTrace();
        }catch(IOException e){
        	e.printStackTrace();
         }finally {
        	 IOUtils.closeQuietly(in);
        }

        String[][] returnArray = new String[result.size()][rowSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = (String[]) result.get(i);
        }
        return returnArray;
    }

	/**
	 * 单元格样式
	 * 
	 * @param wb
	 * @param foreGroudColor
	 * @return
	 */
	private static CellStyle getCellStyle(Workbook wb,short foreGroudColor) {
		// create 3 cell styles
		CellStyle cs = wb.createCellStyle();
		
		// create 2 fonts objects
		Font f = wb.createFont();
		
		//set font 1 to 12 point type
		f.setFontHeightInPoints((short) 10);
		//make it blue
		//f.setColor( (short)0xc );
		// make it bold
		//arial is the default font
		f.setBoldweight(Font.BOLDWEIGHT_BOLD);

		//set cell stlye
		cs.setFont(f);
		cs.setAlignment(CellStyle.ALIGN_CENTER);
		//set the cell format 
//		cs.setDataFormat(df.getFormat("#,##0.0"));
		cs.setBorderBottom(CellStyle.BORDER_THIN);
		cs.setBorderLeft(CellStyle.BORDER_THIN);
		cs.setBorderRight(CellStyle.BORDER_THIN);
		cs.setBorderTop(CellStyle.BORDER_THIN);
		cs.setWrapText(true);
		if(foreGroudColor!=-1) {
			cs.setFillPattern((short) CellStyle.SOLID_FOREGROUND);
			cs.setFillForegroundColor((short)55);
		}
		
		return cs;
	}
	
}
